package com.rickpan.repository;

import com.rickpan.entity.ProjectAnnouncement;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 项目公告数据访问层
 * 
 * @author RickPan Team
 * @since 2024-01-15
 */
@Repository
public interface ProjectAnnouncementRepository extends JpaRepository<ProjectAnnouncement, Long> {

    /**
     * 根据项目ID查找公告列表
     */
    List<ProjectAnnouncement> findByProjectIdOrderByIsPinnedDescCreatedAtDesc(Long projectId);

    /**
     * 根据项目ID查找公告列表（分页）
     */
    Page<ProjectAnnouncement> findByProjectIdOrderByIsPinnedDescCreatedAtDesc(Long projectId, Pageable pageable);

    /**
     * 根据项目ID查找置顶公告
     */
    List<ProjectAnnouncement> findByProjectIdAndIsPinnedTrueOrderByCreatedAtDesc(Long projectId);

    /**
     * 根据项目ID查找非置顶公告
     */
    List<ProjectAnnouncement> findByProjectIdAndIsPinnedFalseOrderByCreatedAtDesc(Long projectId);

    /**
     * 根据项目ID查找非置顶公告（分页）
     */
    Page<ProjectAnnouncement> findByProjectIdAndIsPinnedFalseOrderByCreatedAtDesc(Long projectId, Pageable pageable);

    /**
     * 根据创建者ID查找公告列表
     */
    List<ProjectAnnouncement> findByCreatorIdOrderByCreatedAtDesc(Long creatorId);

    /**
     * 根据创建者ID查找公告列表（分页）
     */
    Page<ProjectAnnouncement> findByCreatorIdOrderByCreatedAtDesc(Long creatorId, Pageable pageable);

    /**
     * 根据项目ID统计公告数量
     */
    long countByProjectId(Long projectId);

    /**
     * 根据项目ID统计置顶公告数量
     */
    long countByProjectIdAndIsPinnedTrue(Long projectId);

    /**
     * 根据创建者ID统计公告数量
     */
    long countByCreatorId(Long creatorId);

    /**
     * 根据项目ID搜索公告
     */
    @Query("SELECT a FROM ProjectAnnouncement a WHERE a.projectId = :projectId " +
           "AND (a.title LIKE %:keyword% OR a.content LIKE %:keyword%) " +
           "ORDER BY a.isPinned DESC, a.createdAt DESC")
    List<ProjectAnnouncement> searchAnnouncementsByProject(@Param("projectId") Long projectId, 
                                                          @Param("keyword") String keyword);

    /**
     * 根据项目ID搜索公告（分页）
     */
    @Query("SELECT a FROM ProjectAnnouncement a WHERE a.projectId = :projectId " +
           "AND (a.title LIKE %:keyword% OR a.content LIKE %:keyword%) " +
           "ORDER BY a.isPinned DESC, a.createdAt DESC")
    Page<ProjectAnnouncement> searchAnnouncementsByProject(@Param("projectId") Long projectId, 
                                                          @Param("keyword") String keyword, 
                                                          Pageable pageable);

    /**
     * 查找最近的公告
     */
    @Query("SELECT a FROM ProjectAnnouncement a WHERE a.projectId = :projectId " +
           "ORDER BY a.createdAt DESC")
    List<ProjectAnnouncement> findRecentAnnouncementsByProject(@Param("projectId") Long projectId, 
                                                              Pageable pageable);

    /**
     * 根据时间范围查找公告
     */
    @Query("SELECT a FROM ProjectAnnouncement a WHERE a.projectId = :projectId " +
           "AND a.createdAt BETWEEN :startDate AND :endDate " +
           "ORDER BY a.isPinned DESC, a.createdAt DESC")
    List<ProjectAnnouncement> findAnnouncementsByDateRange(@Param("projectId") Long projectId,
                                                          @Param("startDate") LocalDateTime startDate,
                                                          @Param("endDate") LocalDateTime endDate);

    /**
     * 根据时间范围查找公告（分页）
     */
    @Query("SELECT a FROM ProjectAnnouncement a WHERE a.projectId = :projectId " +
           "AND a.createdAt BETWEEN :startDate AND :endDate " +
           "ORDER BY a.isPinned DESC, a.createdAt DESC")
    Page<ProjectAnnouncement> findAnnouncementsByDateRange(@Param("projectId") Long projectId,
                                                          @Param("startDate") LocalDateTime startDate,
                                                          @Param("endDate") LocalDateTime endDate,
                                                          Pageable pageable);

    /**
     * 查找今日公告
     */
    @Query("SELECT a FROM ProjectAnnouncement a WHERE a.projectId = :projectId " +
           "AND DATE(a.createdAt) = CURRENT_DATE " +
           "ORDER BY a.isPinned DESC, a.createdAt DESC")
    List<ProjectAnnouncement> findTodayAnnouncementsByProject(@Param("projectId") Long projectId);

    /**
     * 查找本周公告
     */
    @Query("SELECT a FROM ProjectAnnouncement a WHERE a.projectId = :projectId " +
           "AND a.createdAt >= :weekStart " +
           "ORDER BY a.isPinned DESC, a.createdAt DESC")
    List<ProjectAnnouncement> findWeekAnnouncementsByProject(@Param("projectId") Long projectId,
                                                            @Param("weekStart") LocalDateTime weekStart);

    /**
     * 查找本月公告
     */
    @Query("SELECT a FROM ProjectAnnouncement a WHERE a.projectId = :projectId " +
           "AND a.createdAt >= :monthStart " +
           "ORDER BY a.isPinned DESC, a.createdAt DESC")
    List<ProjectAnnouncement> findMonthAnnouncementsByProject(@Param("projectId") Long projectId,
                                                             @Param("monthStart") LocalDateTime monthStart);

    /**
     * 根据项目ID和创建者ID查找公告
     */
    List<ProjectAnnouncement> findByProjectIdAndCreatorIdOrderByCreatedAtDesc(Long projectId, Long creatorId);

    /**
     * 根据项目ID和创建者ID查找公告（分页）
     */
    Page<ProjectAnnouncement> findByProjectIdAndCreatorIdOrderByCreatedAtDesc(Long projectId, Long creatorId, Pageable pageable);

    /**
     * 检查项目中是否存在同标题公告
     */
    boolean existsByProjectIdAndTitle(Long projectId, String title);

    /**
     * 检查项目中是否存在同标题公告（排除指定公告ID）
     */
    @Query("SELECT CASE WHEN COUNT(a) > 0 THEN true ELSE false END FROM ProjectAnnouncement a " +
           "WHERE a.projectId = :projectId AND a.title = :title AND a.id != :excludeId")
    boolean existsByProjectIdAndTitleExcludingId(@Param("projectId") Long projectId, 
                                                @Param("title") String title, 
                                                @Param("excludeId") Long excludeId);
}
